const Mysql = require('../model/db')

class StudentScore{
  // 获取分页学生成绩
  async findPageStudentScore(req,res){
    let currentPage = parseInt(req.query.currentPage)
    // 解决分页只有1页同时删除最后一条数据报警告问题
    // 当最后一条删除后 currentPage - 1 * limit  === 负数
    if(currentPage < 1) currentPage = 1
    let limit = parseInt(req.query.limit)
    // 统计所有学生数量
    let sqlStr = 'select count(*) as total from studentScore'
    let data = [(currentPage-1) * limit,limit]
    // 查询返回的都是以数组形式展开
    try {
      let totalResult = await Mysql.base(sqlStr)
      // if(totalResult[0].total !== 0){
        let sqlLimit = 'select * from studentScore limit ?,?'
        let limitResult = await Mysql.base(sqlLimit,data)
        res.status(200).json({
          code:200,
          message:'ok',
          data:limitResult,
          currentPage,
          limit,
          total:totalResult[0].total  
        })
      // }
    } catch (error) {
      res.status(201).json({
        code:201,
        message:`数据加载失败`,
      })
    }
  }
  // 根据姓名查成绩
  async findStudentName(req,res){
    let {studentName} = req.query
    let sqlStr = `select * from studentScore where studentName like '%${studentName}%'`
    let result = await Mysql.base(sqlStr)
    if(result.length !== 0){
      res.status(200).json({
        code:200,
        message:'ok',
        data:result
      })
    }else{
      res.status(201).json({
        code:201,
        message:'无此学生信息',
      })
    }
  }
  // 查询不及格成绩
  // async findStudentFailScore(req,res){
  //   let {subject,score} = req.query
  //   let sqlStr = `select * from studentScore where ${subject} < ${score}`
  //   let result = await Mysql.base(sqlStr)
  //   if(result.length !== 0){
  //     res.status(200).json({
  //       code:200,
  //       message:'ok',
  //       data:result
  //     })
  //   }else{
  //     res.status(200).json({
  //       code:200,
  //       message:'此课程暂无低于此分数得学生',
  //     })
  //   }
  // }
  // 根据课程和分数查询成绩
  async findStudentSubjectScore(req,res){
    // let a = '[1,5]'
    // let b = JSON.parse(a)
    // console.log(b)
    let {subject,score} = req.query || undefined
    let result = void 0;
    // 当传递过来得分数大于 2 的时候 说明用户正在查找 某一区间 的学生
    // 当传递过来得分数小于 2 的时候 说明用户正在查找 >60 或 <60 的学生
    if(score.length > 2){
      let scoreArr = score.split(',')
      let sqlStr = `select * from studentScore where ${subject} >= ${scoreArr[0]} and ${subject} <= ${scoreArr[1]}`
      result = await Mysql.base(sqlStr)
    }else{
      let sqlStr = `select * from studentScore where ${subject} < ${score}`
      result = await Mysql.base(sqlStr)
    }
    if(result.length !== 0){
      res.status(200).json({
        code:200,
        message:'ok',
        data:result
      })
    }else{
      res.status(200).json({
        code:200,
        message:'此课程暂无低于此分数得学生',
      })
    }
    
  }
  // 添加成绩
  async addStudentScore(req,res){
    let {studentName,ps,ai,ae,html_css,javascript,node,vue} = req.body
    let sqlStr = `insert into studentScore(studentName,ps,ai,ae,html_css,javascript,node,vue) values ?`
    // 利用二维数组， 此时 sql value 只需一个占位符（？）即可 
    let data = [[studentName,ps,ai,ae,html_css,javascript,node,vue]]
    try {
      let result = await Mysql.base(sqlStr,[data])
      if(result.affectedRows === 1){
        res.status(200).json({
          code:200,
          message:'添加成功',
        })
      }
    } catch (error) {
      res.status(201).json({
        code:201,
        message: `添加失败,${error.message}`,
      })
    }
    
  }
  // 修改成绩
  async updateStudentScore(req,res){
    let {studentName,ps,ai,ae,html_css,javascript,node,vue,scoreId} = req.body
    let sqlStr = `update studentScore set studentName=?,ps=?,ai=?,ae=?,html_css=?,javascript=?,node=?,vue=? where scoreId=?`
    let data = [studentName,ps,ai,ae,html_css,javascript,node,vue,scoreId]
    try {
      let result = await Mysql.base(sqlStr,data)
      if(result.affectedRows === 1){
        res.status(200).json({
          code:200,
          message:'修改成功',
        })
      }
    } catch (error) {
      res.status(201).json({
        code:201,
        message: `修改失败,${error.message}`,
      })
    }
  }
  // 删除成绩
  async deleteStudentScore(req,res){
    let {scoreId} = req.query
    let sqlStr = `delete from studentScore where scoreId = ${scoreId}`
    try {
      let result = await Mysql.base(sqlStr)
      if(result.affectedRows >= 1){
        res.status(200).json({
          code:200,
          message:'删除成功',
        })
      }
    } catch (error) {
      res.status(201).json({
        code:201,
        message:`删除失败,${error.message}`,
      })
    }
  }
  // 批量删除成绩
  async batchDeleteStudentScore(req,res){
    let {scoreIdArr} = req.query
    let sqlStr = `delete from studentScore where scoreId in(${scoreIdArr})`
    try {
      let result = await Mysql.base(sqlStr)
      if(result.affectedRows >= 1){
        res.status(200).json({
          code:200,
          message:'删除成功',
        })
      }
    } catch (error) {
      res.status(201).json({
        code:201,
        message: '批量删除失败,请选择需要删除得数据',
      })
    }
  }
}

// 将此构造函数暴露出去
module.exports = new StudentScore